package demo.utils;

import cn.hutool.core.collection.CollectionUtil;
import com.lkcoffee.demo.domain.ColMappingDto;
import com.lkcoffee.demo.domain.NormalSqlStructureDto;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.lang3.StringUtils;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.StringReader;
import java.io.UncheckedIOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @Desciption:
 * @Author: feixiang.li
 * @date: 2024-07-29 17:28
 **/
@Slf4j
public class SqlToMarkdownUtils {

    public static void writeMarkdownFile(String filePath, List<ColMappingDto> content) {
        try {
            Files.deleteIfExists(Paths.get(filePath)); // 如果文件存在，则删除
            BufferedWriter writer = Files.newBufferedWriter(Paths.get(filePath), StandardCharsets.UTF_8);
            writer.write("| 字段名 | 注释  | 类型 |\n");
            writer.write("| --- | --- | --- |\n");
            for (ColMappingDto item : content) {
                String line = String.format("| %s | %s | %s |\n", item.getName(), item.getComment(), item.getType());
                writer.write(line);
            }
            writer.close();  // 写入完成后关闭文件流
        } catch (IOException e) {
            throw new UncheckedIOException("Error writing to Markdown file", e);
        }
    }

    public static void writeMarkdownFile(String filePath, NormalSqlStructureDto content) {
        try {
            Files.deleteIfExists(Paths.get(filePath)); // 如果文件存在，则删除
            BufferedWriter writer = Files.newBufferedWriter(Paths.get(filePath), StandardCharsets.UTF_8);
            writer.write(String.format("# %s\n", content.getTableNames()));
            writer.write(String.format("## %s\n", content.getComment()));
            writer.write("| 字段名 | 注释  | 类型 |\n");
            writer.write("| --- | --- | --- |\n");
            for (ColMappingDto item : content.getColMappings()) {
                String line = String.format("| %s | %s | %s |\n", item.getName(), item.getComment(), item.getType());
                writer.write(line);
            }
            writer.close();  // 写入完成后关闭文件流
        } catch (IOException e) {
            throw new UncheckedIOException("Error writing to Markdown file", e);
        }
    }

    /**
     * 构建表名和表别名的对应关系
     *
     * @param tableMapping
     * @param sql
     * @param tblAlias
     */
    private static void buildTblMapping(Map<String, Object> tableMapping, String sql, String tblAlias) {
        if (StringUtils.isNotEmpty(tblAlias)) {
            if (CollectionUtil.isEmpty(tableMapping) || Objects.isNull(tableMapping.get(tblAlias))) {
                sql = sql.replaceAll("(?i)\\s+as\\s+", " ");
                String regex = "(from|join)\\s+(\\w+\\.)?\\w+\\s+".concat(tblAlias).concat("\\s+");
                Pattern p = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
                Matcher m = p.matcher(sql.replaceAll("[\n\r]", " "));
                String replaceReg = "(?i)(from|join|" + tblAlias + ")";
                while (m.find()) {
                    tableMapping.put(tblAlias, m.group(0).replaceAll(replaceReg, "").trim());
                }
            }
        }
    }

    /**
     * 使用 Stream API 查找指定元素后的一个元素。
     *
     * @param target     要查找的目标元素
     * @param stringList 字符串列表
     * @return 目标元素后面的一个元素，如果目标元素不存在或已经是最后一个，则返回空字符串
     */
    public static String findFollowingElement(String target, List<String> stringList) {
        if (StringUtils.isBlank(target)) {
            return "";
        }
        Optional<String> optionalS = stringList.stream()
                .filter(target::equalsIgnoreCase)
                .map(stringList::indexOf)
                .map(index -> stringList.subList(index + 1, stringList.size()))
                .flatMap(Collection::stream)
                .findFirst();
        String result = optionalS.orElse("");
        // 使用正则表达式去除开头和结尾的单引号或双引号
        result = result.replaceAll("^['\"]|['\"]$", "");
        return result;
    }

    public static String readSqlFile(String filePath) {
        try {
            return Files.readString(Paths.get(filePath));
        } catch (IOException e) {
            throw new RuntimeException("Error reading SQL file", e);
        }
    }

    public static NormalSqlStructureDto convertSqlToMarkdown(String sql) {
        log.info("开始转换-> {}",sql);
        CCJSqlParserManager parserManager = new CCJSqlParserManager();
        NormalSqlStructureDto normalSqlStructureDto = new NormalSqlStructureDto();
        try {
            // 构建表
            CreateTable createTableStatement = (CreateTable) parserManager.parse(new StringReader(sql));
            // 创建表名发现者对象
            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            // 获取到表名列表
            Set<String> tableNameList = tablesNamesFinder.getTables(createTableStatement);
            if(tableNameList.isEmpty()){
                log.info("对不起表为空");
                return null;
            }
            String tableName = new ArrayList<>(tableNameList).get(0);
            normalSqlStructureDto.setTableNames(tableName);
            normalSqlStructureDto.setComment(findFollowingElement("comment",createTableStatement.getTableOptionsStrings()));
            // 表别名映射
            List<ColumnDefinition> columnDefinitions = createTableStatement.getColumnDefinitions();
            normalSqlStructureDto.setColMappings(columnDefinitions.stream()
                    .map(cd -> {
                        ColMappingDto colMappingDto = new ColMappingDto();
                        colMappingDto.setName(cd.getColumnName());
                        colMappingDto.setTableName(tableName);
                        colMappingDto.setType(cd.getColDataType().toString());
                        colMappingDto.setComment(findFollowingElement("comment", cd.getColumnSpecs()));
                        return colMappingDto;
                    })
                    .collect(Collectors.toList()));
            return normalSqlStructureDto;
        } catch (JSQLParserException e) {
            throw new RuntimeException("Error parsing SQL", e);
        }
    }


}
